package com.buaa.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.buaa.dto.LocationDTO;
import com.buaa.entity.DriverAccidentDesc;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DriverAccidentDescMapper extends BaseMapper<DriverAccidentDesc> {



    @Select("select vehicle_type AS district ,count(1) as users from case_bean\n" +
            "where vehicle_type is not null\n" +
            "group by  vehicle_type\n" +
            "having  count(1) >= 2")
    List<LocationDTO>  byDriverAccidentVehicleType();

    @Select("select gender AS district ,count(1) as users from case_bean\n" +
            "where gender is not null\n" +
            "group by  gender;")
    List<LocationDTO>  byDriverAccidentGender();

    @Select("select  TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS district , count(1) as users \n" +
            "from case_bean where birth_date is not null\n" +
            "group by TIMESTAMPDIFF(YEAR, birth_date, CURDATE())")
    List<LocationDTO>  byDriverAccidentAge();

    @Select("select cast(time as char) as district, users from (\n" +
            "select convert(replace(replace(replace(substr(accident_time,1,2),'时',''),'凌晨','0'),'03','3'), unsigned ) as time,count(1) as users\n" +
            "from case_bean where accident_time is not null\n" +
            "group by  convert( replace(replace(replace(substr(accident_time,1,2),'时',''),'凌晨','0'),'03','3'), unsigned )\n" +
            "order by time) a order by users desc")
    List<LocationDTO>  byDriverAccidentTime();
}
